package com.cslg.ac.demo.dao.host;

import com.cslg.ac.demo.dao.host.po.HostPO;
import com.cslg.ac.demo.util.integer.IntegerUtil;
import org.apache.ibatis.annotations.*;
import org.springframework.beans.FatalBeanException;

import java.util.List;

@Mapper
public interface HostDAO {

    String TABLE_NAME = "e_p_host";
    String INSERT_FIELDS = "id, name, address, phone, description, service, attention, pet_exp, latitude, longitude";
    String SELECT_FIELDS = "id, name, address, phone, description, service, attention, pet_exp petExp,pet_image petImage," +
            " image, latitude, longitude, credit";
    String VALUE_FIELDS = "#{id}, #{name}, #{address}, #{phone}, #{description}, #{service}," +
            " #{attention}, #{petExp}, #{latitude}, #{longitude}";

    @Insert({"insert into ",TABLE_NAME,"(",INSERT_FIELDS,") values(",VALUE_FIELDS,")"})
    void insert(HostPO hostPO);

    @Update({"update ", TABLE_NAME, "set image = #{image} where id = #{id}" })
    void updateImage(@Param("image") String image, @Param("id") int id);

    @Update({"update ", TABLE_NAME, "set pet_image = #{petImage}"})
    void updatePetImage(@Param("petImage")String petImage, @Param("id") int id);

    @Update({"update ", TABLE_NAME, " set name = #{name}, address = #{address}, phone = #{phone}, description = #{description}, service = #{service}, " +
            "attention = #{attention}, pet_exp = #{petExp}, pet_image = #{petImage}, latitude = #{latitude}, longitude = #{longitude}, credit = #{credit} " +
            "where id = #{id}" })
    void updateById(HostPO hostPO);

    @Select({"select id from ",TABLE_NAME," where name like concat(concat('%',#{name}),'%') limit #{offset}, #{num}"})
    Integer[] selectIdByName(@Param("name") String name, @Param("offset") int offset, @Param("num") int num);

    @Select({"select ",SELECT_FIELDS,"from ",TABLE_NAME,"where id = #{id}"})
    HostPO selectById(@Param("id") int id);

    @Select({"select id from ",TABLE_NAME," where address like concat(concat('%',#{address}),'%') limit #{offset}, #{num}"})
    Integer[] selectIdByAddress(@Param("address") String address, @Param("offset") int offset, @Param("num") int num);

    @Select({"select id from ", TABLE_NAME, "order by credit desc limit #{offset}, #{num}"})
    Integer[] sortByCredit(@Param("offset")int offset, @Param("num")int num);

    @Select({"select id,(power(mod(abs(longitude - #{longitude}),360),2) + power(abs(latitude - #{latitude}),2)) as distance from", TABLE_NAME, " order by" +
            " distance limit #{offset}, #{num} "})
    Integer[] sortByLatAndLong(@Param("longitude")double longitude, @Param("latitude")double latitude , @Param("offset")int offset, @Param("num")int num);

    @Select({"select id,(power(mod(abs(longitude - #{longitude}),360),2) + power(abs(latitude - #{latitude}),2)) as distance from", TABLE_NAME, " order by" +
            " concat(distance,credit) limit #{offset}, #{num} "})
    Integer[] sortByLatAndLongAndCredit(@Param("longitude")double longitude, @Param("latitude")double latitude , @Param("offset")int offset, @Param("num")int num);

    @Select({"select id,(power(mod(abs(longitude - #{longitude}),360),2) + power(abs(latitude - #{latitude}),2)) as distance from", TABLE_NAME, " where address like concat(concat('%',#{address}),'%') order by" +
            " concat(distance,credit) limit #{offset}, #{num} "})
    Integer[] sortByThree(@Param("longitude")double longitude, @Param("latitude")double latitude,@Param("address")String address, @Param("offset")int offset, @Param("num")int num);

    @Select({"select id from", TABLE_NAME, " where address like concat(concat('%',#{address}),'%') order by" +
            " credit limit #{offset}, #{num} "})
    Integer[] sortByAddressAndCredit(@Param("address")String address, @Param("offset")int offset, @Param("num")int num);

    @Select({"select id,(power(mod(abs(longitude - #{longitude}),360),2) + power(abs(latitude - #{latitude}),2)) as distance from", TABLE_NAME, " where address like concat(concat('%',#{address}),'%') order by" +
            " distance limit #{offset}, #{num} "})
    Integer[] sortByAddressAndDistance(@Param("longitude")double longitude, @Param("latitude")double latitude,@Param("address")String address, @Param("offset")int offset, @Param("num")int num);

}
